<!--- ***************************************************

2013-2014 Data Procedures

This includes scripts used to:

	Remove all deleted PB records from database
	Migrate data-entered PB exit surveys from temp to live tables (from this point all steps are applied to both PB and Web surveys)
	Fix capitalization for name, address, city, high school, etc.
	Format phones (###-###-####)
	Format emails
	QA SSN (4 digits), Zip_Code (5 digits), phones (10 digits + 2 dashes) and DOB (valid date)
	Validate (check existence of) City and Zip_Code
	Validate DOB is in range
	Fix what's fixable after validation
	Extract cody of data for GDS matching
	Convert text to uppercase for matching (Name, Address and City)
	Format phones for matching (##########)
	Format DOB for matching (YYYYMMDD)
	Export matching data to Excel and encrypt with password
	Upload data to Google Drive
	
*************************************************** --->
<cfsetting requesttimeout="600">

<cfscript>
	storedProcedureGateway = new assets.calgrants.com.gateway.StoredProcedureGateway();
	gdsMatchScores = storedProcedureGateway.getGDSMatchScores();
</cfscript>

<cfloop query="gdsMatchScores">
	<cfquery name="UpdateGDSMatch" datasource="WMS">
		update GDS_Match_2014_3
		set Score = #Score#,
		Length = #Length#,
		Distance = #Distance#,
		Bonus = #IIf(Phone_Score eq 1, 1, 0)#
		where GDS_Match_ID = #GDS_Match_ID#
	</cfquery>
</cfloop>

<cfoutput>
	#gdsMatchScores.recordCount#
</cfoutput>

<cfabort>


<!--- GDS Match Scoring scripts
<cfquery name="GetMatches" datasource="WMS">
select Student_ID, Student_Score, GDS_Score from [GDS_Match_2014_3]
where score is null
</cfquery>

<cfloop query="GetMatches">
	<cfif Len(Student_Score) gt 0 and Len(GDS_Score) gt 0>
		<cfif Student_Score gt GDS_Score>
			<cfquery name="UpdateScore" datasource="WMS">
				update GDS_Match_2014_3
				set Score = #Student_Score#
				where Student_ID = #Student_ID#
			</cfquery>
		<cfelse>
			<cfquery name="UpdateScore" datasource="WMS">
				update GDS_Match_2014_3
				set Score = #GDS_Score#
				where Student_ID = #Student_ID#
			</cfquery>
		</cfif>
	<cfelse>
		<cfquery name="UpdateScore" datasource="WMS">
			update GDS_Match_2014_3
			set Score = 0
			where Student_ID = #Student_ID#
		</cfquery>
	</cfif>
</cfloop> --->


<!--- 
Nullify blank fields

<cfquery name="GetGDSForCols" datasource="WMS">
	select top 1 *
	from GDS
</cfquery>

<cfloop index="col" list="#GetGDSForCols.ColumnList#">
	<cfif col neq "GDS_ID" and col neq "Student_ID" and col neq "Deleted">
		<cfquery name="SetColBlanksToNull" datasource="WMS">
			update GDS
			set [#col#] = Null
			where [#col#] = ''
		</cfquery>
	</cfif>
</cfloop> --->

<cffunction name="titleCase" access="public" returntype="string">
	<cfargument name="str" type="string" required="yes" />
	<cfscript>
	   var j = 1; 
	   var m = 1;
	   var doCap = true;
	   var tempVar = "";
	   /* Make each word in text an array variable */
	   var Words = ListToArray(LCase(Trim(arguments.str)), " ");
	   var excludeWords = StructNew();
	   var ResetToALLCAPS = StructNew();
	   /* Words to never capitalize */
	   //tempVar =  ListToArray("a,above,after,ain't,among,an,and,as,at,below,but,by,can't,don't,for,from,from,if,in,into,it's,nor,of,off,on,on,onto,or,over,since,the,to,under,until,up,with,won't");
	   tempVar = ListToArray("of,and");
	   for(j=1; j LTE (ArrayLen(tempVar)); j = j+1){
			   excludeWords[tempVar[j]] = 0;
	   }
	   /* Words to always capitalize */
	   tempVar = ListToArray("II,III,IV,V,VI,VII,VIII,IX,X,XI,XII,XIII,XIV,XV,XVI,XVII,XVIII,XIX,XX,XXI");
	   for(j=1; j LTE (ArrayLen(tempVar)); j = j+1){
			   ResetToALLCAPS[tempVar[j]] = 0;
	   }
	   /* Check words against exclude list */
	   for(j=1; j LTE (ArrayLen(Words)); j = j+1){
			   doCap = true;
			   /* Word must be less than four characters to be in the list of excluded words */
			   if(LEN(Words[j]) LT 4){
					   if(StructKeyExists(excludeWords,Words[j])){ doCap = false; }
			   }
			   /* Capitalize hyphenated words */
			   if(ListLen(trim(Words[j]),"-") GT 1){
					   for(m=2; m LTE ListLen(Words[j], "-"); m=m+1){
							   tempVar = ListGetAt(Words[j], m, "-");
							   tempVar = UCase(Mid(tempVar,1, 1)) & Mid(tempVar,2, Len(tempVar)-1);
							   Words[j] = ListSetAt(Words[j], m, tempVar, "-");
					   }
			   }
			   /* Automatically capitalize first and last words */
			   if(j eq 1 or j eq ArrayLen(Words)){ doCap = true; }
			   /* Capitalize qualifying words */
			   if(doCap){ Words[j] = UCase(Mid(Words[j],1, 1)) & Mid(Words[j],2, Len(Words[j])-1); }
			   if (structKeyExists(ResetToALLCAPS, Words[j])) Words[j] = UCase(Words[j]);
	   }
	   return ArrayToList(Words, " ");
	</cfscript>
</cffunction>
<!--- remove any double spaces, quotes, dashes, slashes, commas and pounds --->
<cffunction name="dedouble" output="false" access="public" returntype="string">
	<cfargument name="str" required="yes" />
	<cfset var retStr = str />
	<cfset retStr = Replace(retStr, "  ", " ", "all") />
	<cfset retStr = Replace(retStr, "''", "'", "all") />
	<cfset retStr = Replace(retStr, "--", "-", "all") />
	<cfset retStr = Replace(retStr, "//", "/", "all") />
	<cfset retStr = Replace(retStr, ",,", ",", "all") />
	<cfset retStr = Replace(retStr, "####", "##", "all") />
	<cfset retStr = Replace(retStr, '""', '"', "all") />
	<cfset retStr = Replace(retStr, "  ", " ", "all") />
	<cfreturn Trim(retStr) />
</cffunction>
<cffunction name="formatPhone" output="false" access="public" returntype="string">
	<cfargument name="phone" type="string" required="yes" />
	<cfset var fPhone = "">
	<cfset var ret = "">
	<cfset fPhone = Replace(arguments.phone, "(", "", "all")>
	<cfset fPhone = Replace(fPhone, ")", "", "all")>
	<cfset fPhone = Replace(fPhone, " ", "", "all")>
	<cfset fPhone = Replace(fPhone, "-", "", "all")>
	<cfset fPhone = Replace(fPhone, ".", "", "all")>
	<cfif len(fPhone) gte 10>
		<cfset fPhone = Right(fPhone, 10)>
		<cfset ret = Left(fPhone, 3) & "-" & Mid(fPhone, 4, 3) & "-" & Right(fPhone, 4)>
	</cfif>
	<cfreturn ret>
</cffunction>
<cffunction name="convertGDSDate" output="false" access="public" returntype="any">
	<cfargument name="gdsDate" required="yes" />
	<cfset var yr = "" />
	<cfset var mo = "" />
	<cfset var dy = "" />
	<cfset var dateVal = "" />
	<cfif Len(Trim(arguments.gdsDate)) eq 8>
		<cfset yr = Left(arguments.gdsDate, 4) />
		<cfset mo = Mid(arguments.gdsDate, 5, 2) />
		<cfset dy = Right(arguments.gdsDate, 2) />
		<cfset dateVal = mo & "/" & dy & "/" & yr />
		<cfreturn DateFormat(dateVal, "m/d/yyyy") />
	<cfelse>
		<cfreturn "" />
	</cfif>
</cffunction>
<cffunction name="formatShortDate" output="false" access="public" returntype="string">
	<cfargument name="dateVal" required="yes" />
	<cfif IsDate(arguments.dateVal)>
		<cfreturn DateFormat(arguments.dateVal, "m/d/yyyy") />
	<cfelse>
		<cfreturn "" />
	</cfif>
</cffunction>
<cffunction name="formatZipCode" output="false" access="public" returntype="string">
	<cfargument name="zipCode" type="string" required="yes" />
	<cfif NOT IsValid("zipcode", Trim(arguments.zipCode))>
		<cfreturn "" />
	<cfelse>
		<cfreturn Left(Trim(arguments.zipCode), 5) />
	</cfif>
</cffunction>

<cfsetting requesttimeout="600">

<!--- ^^^^^^^^^^^
	Remove all deleted PB records from database that were double entries or true orphans - "deleteDeleted"
^^^^^^^^^^^ --->

<cfparam name="deleteDeleted" default="false">
<cfif deleteDeleted>
	<!--- Get list of Student_ID for deleted records --->
	<cfquery name="GetDeletedStudentIDsFromStudent2014PB" datasource="WMS">
	select Student_ID from Student_2014_PB where Deleted = 1
	</cfquery>
	<cfloop query="GetDeletedStudentIDsFromStudent2014PB">
		<!--- Delete from Evaluation_2014_PB --->
		<cfquery name="DeleteEvaluation2014PB" datasource="WMS">
			delete from Evaluation_2014_PB where Student_ID = #Student_ID#
		</cfquery>
		<!--- Delete from Student_2014_PB --->
		<cfquery name="DeleteStudent2014PB" datasource="WMS">
			delete from Student_2014_PB where Student_ID = #Student_ID#
		</cfquery>
	</cfloop>
</cfif>

<!--- ^^^^^^^^^^^
	Migrate PB data to live tables
^^^^^^^^^^^ --->

<cfparam name="migratePB" default="false">
<cfparam name="recsToMigrate" default="10">
<cfif migratePB>
	<!--- Get Student_2014_PB data --->
	<cfquery name="GetStudent2014PB" datasource="WMS" maxrows="#recsToMigrate#">
		select 
		Student_2014_PB.Student_ID	
		,[Workshop_ID]
		,[First_Name]
		,[Last_Name]
		,[Address]
		,[City]
		,[Zip_Code]
		,[DOB]
		,[Phone1]
		,[Phone2]
		,[Email]
		,[SSN_Code]
		,[SSN]
		,[Evaluation_ID]
		,[q1]
		,[q2]
		,[q3]
		,[q4]
		,[q5]
		,[q6]
		,[q7]
		,[q8]
		,[q9]
		,[q9_other]
		,[q10]
		,[q11]
		,[q12]
		,[q12_other]
		,[q13]
		,[q13_other]
		,[q14]
		,[q14_other]
		,[q15]
		,[q16]
		,[q16_other]
		,[q17]
		,[q18]
		,[q18_other]
		,[q19]
		,[q20]
		 from Student_2014_PB 
		 left outer join Evaluation_2014_PB 
		 on Evaluation_2014_PB.Student_ID = Student_2014_PB.Student_ID
		 where Student_2014_PB.Migrated = 0
		 and Student_2014_PB.Deleted = 0
	</cfquery>
	<!--- Get Evaluation_2014_PB data
	<cfquery name="GetEvaluation2014PB" datasource="WMS">
		select * from Evaluation_2014_PB
	</cfquery> --->
	<cfloop query="GetStudent2014PB">
		<cfquery name="InsertStudent" datasource="WMS">
			INSERT INTO Student
           (
			   [Origin]
			   ,[Workshop_ID]
			   ,[First_Name]
			   ,[Last_Name]
			   ,[Address]
			   ,[City]
			   ,[Zip_Code]
			   ,[DOB]
			   ,[Phone1]
			   ,[Phone2]
			   ,[Email]
			   ,[SSN_Code]
			   ,[SSN]
			   ,[Date_Created]
			  ,[_Student_ID_2014_PB]
		  )
     VALUES
           (
			   'Paper'
			   ,<cfqueryparam cfsqltype="cf_sql_integer" value="#Workshop_ID#" null="#IIf(Len(Trim(Workshop_ID)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(First_Name)#" null="#IIf(Len(Trim(First_Name)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Last_Name)#" null="#IIf(Trim(Trim(Last_Name)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Address)#" null="#IIf(Trim(Trim(Address)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(City)#" null="#IIf(Len(Trim(City)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Zip_Code)#" null="#IIf(Len(Trim(Zip_Code)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_date" value="#formatShortDate(DOB)#" null="#IIf(NOT IsDate(formatShortDate(DOB)), DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#formatPhone(Phone1)#" null="#IIf(Len(formatPhone(Phone1)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#formatPhone(Phone2)#" null="#IIf(Len(formatPhone(Phone2)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(LCase(Email))#" null="#IIf(Len(Trim(Email)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(SSN_Code)#" null="#IIf(Len(Trim(SSN_Code)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(SSN)#" null="#IIf(Len(Trim(SSN)) eq 0, DE(true), DE(false))#">
			   ,#Now()#
			   ,<cfqueryparam cfsqltype="cf_sql_integer" value="#Student_ID#" null="#IIf(Len(Trim(Student_ID)) eq 0, DE(true), DE(false))#">
           )
		   select @@identity as studentID
		</cfquery>
		<cfquery name="InsertEvaluation" datasource="WMS">
			INSERT INTO [WMS].[dbo].[Evaluation]
			   (
			   Deleted
			   ,[Student_ID]
			   ,[q1]
			   ,[q2]
			   ,[q3]
			   ,[q4]
			   ,[q5]
			   ,[q6]
			   ,[q7]
			   ,[q8]
			   ,[q9]
			   ,[q9_other]
			   ,[q10]
			   ,[q11]
			   ,[q12]
			   ,[q12_other]
			   ,[q13]
			   ,[q13_other]
			   ,[q14]
			   ,[q14_other]
			   ,[q15]
			   ,[q16]
			   ,[q16_other]
			   ,[q17]
			   ,[q18]
			   ,[q18_other]
			   ,[q19]
			   ,[q20]
			   ,[Date_Created]
			   ,[_Evaluation_ID_2014_PB]
			   )
		 VALUES
			   (
			   0
			   ,#InsertStudent.studentID#
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q1)#" null="#IIf(Len(Trim(q1)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q2)#" null="#IIf(Len(Trim(q2)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q3)#" null="#IIf(Len(Trim(q3)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q4)#" null="#IIf(Len(Trim(q4)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q5)#" null="#IIf(Len(Trim(q5)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q6)#" null="#IIf(Len(Trim(q6)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q7)#" null="#IIf(Len(Trim(q7)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q8)#" null="#IIf(Len(Trim(q8)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q9)#" null="#IIf(Len(Trim(q9)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q9_other)#" null="#IIf(Len(Trim(q9_other)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q10)#" null="#IIf(Len(Trim(q10)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q11)#" null="#IIf(Len(Trim(q11)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q12)#" null="#IIf(Len(Trim(q12)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q12_other)#" null="#IIf(Len(Trim(q12_other)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q13)#" null="#IIf(Len(Trim(q13)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q13_other)#" null="#IIf(Len(Trim(q13_other)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q14)#" null="#IIf(Len(Trim(q14)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q14_other)#" null="#IIf(Len(Trim(q14_other)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q15)#" null="#IIf(Len(Trim(q15)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q16)#" null="#IIf(Len(Trim(q16)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q16_other)#" null="#IIf(Len(Trim(q16_other)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q17)#" null="#IIf(Len(Trim(q17)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q18)#" null="#IIf(Len(Trim(q18)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q18_other)#" null="#IIf(Len(Trim(q18_other)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q19)#" null="#IIf(Len(Trim(q19)) eq 0, DE(true), DE(false))#">
			   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(q20)#" null="#IIf(Len(Trim(q20)) eq 0, DE(true), DE(false))#">
			   ,#Now()#
			   ,<cfqueryparam cfsqltype="cf_sql_integer" value="#Evaluation_ID#" null="#IIf(Len(Trim(Evaluation_ID)) eq 0, DE(true), DE(false))#">
			  )
		   	select @@identity as evaluationID
		</cfquery>
		<!--- update Migrated and Student_ID_Live in Student_2014_PB --->
		<cfquery name="UpdateStudent" datasource="WMS">
			update Student_2014_PB
			set Migrated = 1,
			Student_ID_Live = #InsertStudent.studentID#
			where Student_ID = #Student_ID#
		</cfquery>
		
		<!--- update Migrated and Student_ID_Live in Evaluation_2014_PB --->
		<cfquery name="UpdateStudent" datasource="WMS">
			update Evaluation_2014_PB
			set Migrated = 1,
			Evaluation_ID_Live = #InsertEvaluation.evaluationID#
			where Student_ID = #Student_ID#
		</cfquery>
	</cfloop>
	<cfoutput>Migrated #GetStudent2014PB.RecordCount# records</cfoutput>
</cfif>

<!--- ^^^^^^^^^^^
	Cleanup, validate, review/fix and format migrated data in Student
	
	--------------------------------------------------------------------
	--------------------------------------------------------------------
	
		Cleanup - purge commas, spaces, single quotes and double quotes
		--------------------------------------
		
			All fields
				Trim spaces
				Convert mid double spaces to single space
				Fix disallowed special characters 
					!
					@ except Email
					# except Address
					$
					%
					^
					&
					*
					(
					)
					_ (underscore) except Email
					+
					=
					[
					{
					]
					}
					\
					|
					;
					:
					' except First_Name, Last_Name, Address, City
					"
					, corrupts CSV data format and must be removed
					<
					. except First_Name, Last_Name, Address, Email
					>
					/
					?
					plus any foreign language or combination-key characters
					
					Name: REReplace(UCase(Trim(First_Name)), "[^A-Z0-9 -]", "", "all")
					City: REReplace(UCase(Trim(First_Name)), "[^A-Z0-9 -]", "", "all")
					
	
			First_Name, Last_Name, City, Address
				Remove commas but preserve a single space after (ie ,<space>)
				Allow single quotes but trim spaces (ie abc'def)
				Convert to Title Case
	
			Email
				Convert to all lowercase
				
			Phone
				Format to ###-###-####
	
		Validation - if violation found, skip update and mark/code for manual review
		--------------------------------------
		Special characters (except spaces, single quotes and dashes) are not allowed, flag for manual review if found
		Consecutive spaces, single quotes and dashes are not allowed, flag for manual review if found
		
		Student_ID is required and must be an integer
		First_Name is required and must be a string
		Last_Name is required and must be a string
		City is required and must be a string
		Address is required and must be a string
		Zip_Code is required and must contain at least 5 digits
		DOB is required and must be a valid date in an acceptable range
		Email must be in email format, otherwise null
		Phone1 must contain 10 digits, otherwise null
		Phone2 must contain 10 digits, otherwise null
		SSN must contain 4 digits, otherwise null
		Q7 must be an integer from 1-3
	
		
		Format
		--------------------------------------
		Apply title case to First_Name, Last_Name and City.  Apply all lower case to Email
		Format Phone1 and Phone2 to ###-###-####
		
		
	and (Address like 'po %' or Address like 'p.o. %')
^^^^^^^^^^^ --->

<cfparam name="cleanupStudents" default="false" />
<cfparam name="doStudentUpdate" default="false" />
<cfif cleanupStudents>
	<cfquery name="GetStudents" datasource="WMS" maxrows="1">
		select Student_ID, First_Name, Last_Name, Address, City, Zip_Code, Email, DOB, Phone1, Phone2, SSN
		from Student
		where Deleted = 0
		and _Processed_2014 = 0
		order by Student_ID desc
	</cfquery>
	<cfoutput>
		<table border="1">
			<cfloop query="GetStudents">
				<cfset _firstName = TitleCase(REReplace(Dedouble(Trim(First_Name)), "[^a-zA-Z\ \-\']", "", "all")) />
				<cfset _lastName = TitleCase(REReplace(Dedouble(Trim(Last_Name)), "[^a-zA-Z\ \-\']", "", "all")) />
				<cfset _address = TitleCase(REReplace(Dedouble(Trim(Address)), "[^a-zA-Z0-9\ \,\.\##\/\-]", "", "all")) />
				<cfset _city = TitleCase(REReplace(Dedouble(Trim(City)), "[^a-zA-Z\ \-']", "", "all")) />
				<cfset _zipCode = Left(REReplace(Dedouble(Trim(Zip_Code)), "[^0-9]", "", "all"), 5) />
				<cfset _email = LCase(REReplace(Dedouble(Trim(Email)), "[^a-zA-Z0-9\-\_\@\.\']", "", "all")) />
				<cfset _dob = DateFormat(DOB, "yyyy-mm-dd") />
				<cfset _phone1 = formatPhone(REReplace(Dedouble(Trim(Phone1)), "[^0-9]", "", "all")) />
				<cfset _phone2 = formatPhone(REReplace(Dedouble(Trim(Phone2)), "[^0-9]", "", "all")) />
				<cfset _ssn = TitleCase(REReplace(Dedouble(Trim(SSN)), "[^0-9\']", "", "all")) />
				
				<cfset _address = ReplaceNoCase(_address, "p.o. ", "PO ") />
				<cfset _address = ReplaceNoCase(_address, "po ", "PO ") />
				<cfset _address = ReplaceNoCase(_address, "p.o ", "PO ") />
				<cfset _address = ReplaceNoCase(_address, "po. ", "PO ") />

				<cfif doStudentUpdate>
				#Student_ID#<br />
					<cfquery name="UpdateStudent" datasource="WMS">
						update Student
						set 
						First_Name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_firstName#" null="#IIf(Len(_firstName) eq 0, true, false)#" />,
						Last_Name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_lastName#" null="#IIf(Len(_lastName) eq 0, true, false)#" />,
						Address = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_address#" null="#IIf(Len(_address) eq 0, true, false)#" />,
						City = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_city#" null="#IIf(Len(_city) eq 0, true, false)#" />,
						Zip_Code = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_zipCode#" null="#IIf(Len(_zipCode) eq 0, true, false)#" />,
						Email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_email#" null="#IIf(Len(_email) eq 0, true, false)#" />,
						DOB = <cfqueryparam cfsqltype="cf_sql_date" value="#_dob#" null="#IIf(NOT IsDate(_dob), true, false)#" />,
						Phone1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_phone1#" null="#IIf(Len(_phone1) eq 0, true, false)#" />,
						Phone2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_phone2#" null="#IIf(Len(_phone2) eq 0, true, false)#" />,
						SSN = <cfqueryparam cfsqltype="cf_sql_varchar" value="#_ssn#" null="#IIf(Len(_ssn) eq 0, true, false)#" />,
						[_Processed_2014] = 1
						where Student_ID = #Student_ID#
					</cfquery>
				<cfelse>
					<tr>
						<td>#Student_ID#</td>
						<td>#_firstName#</td>
						<td>#_lastName#</td>
						<td>#_address#</td>
						<td>#_city#</td>
						<td>#_zipCode#</td>
						<td>#_email#</td>
						<td>#_dob#</td>
						<td>#_phone1#</td>
						<td>#_phone2#</td>
						<td>#_ssn#</td>
					</tr>
				</cfif>
			</cfloop>
		</table>
	</cfoutput>
</cfif>

<!---


	Migrate first round GDS data from file to table to master GDS table
	
	GDS matching import data is imported into a GDS_Match_yyyymmdd table where it is scored and match verified. Scored and match verified data is then migrated to GDS (master) where it is validated (dates, phones, zip codes, etc.) and formatted (title-case strings (first name, last name, address, city, etc) and format applicable fields (phone, email, zip code, etc).
	
	Steps are...

	- Data is imported into the GDS_Match_yyyymmdd table using DTS
	- After importing into GDS_Match_yyyymmdd table, stats and scores are recorded into the GDS_Match_yyyymmdd table
	- Next match-verify (via WMS UI) GDS_Match_yyyymmdd data (not the master GDS)
	- Match-verified data is migrated from GDS_Match_ to GDS using DTS (scrutinize bit and date fields)
	- Migrated data in master GDS is validated (dates, phones, zips, etc) and formatted (title-case, phone, email, zip, etc)

 --->

<!---*************************
	Update bit and date "Temp" fields in GDS
**************************--->
<cfparam name="updateGDSTemps" default="false" />
<cfparam name="processQty" default="1000" />
<cfif updateGDSTemps>
	<cfset lastGDSID = 0 />
	<cfquery name="GetGDSTemps" datasource="WMS" maxrows="#processQty#">
		select [GDS_ID]
		,[Deleted]
		,[Student_ID]
		,[Survey Entry ID]
		,[SSN Last4]
		,[Match_Criteria_Nbr]
		,[Match_Record_Nbr]
		,[SSN_Nbr]
		,[_Stdt_St_Res_Date_Temp]
		,[Stdt_St_Res_Date]
		,[_HS_Grad_Date_Temp]
		,[HS_Grad_Date]
		,[_Pell_Grant_Elig_Flag_Temp]
		,[Pell_Grant_Elig_Flag]
		,[_Rcv_Public_Bnft_Temp]
		,[Rcv_Public_Bnft]
		,[_On_Time_ISIR_Temp]
		,[On_Time_ISIR]
		,[_ISIR_Date_Temp]
		,[ISIR_Date]
		,[_On_Time_GPA_Temp]
		,[On_Time_GPA]
		,[_GPA_Date_Temp]
		,[GPA_Date]
		,[_Non_SSN_GPA_Temp]
		,[Non_SSN_GPA]
		,[_On_Time_Dream_Act_Cal_ISIR_Temp]
		,[On_Time_Dream_Act_Cal_ISIR]
		,[_WebGrants_Account_Temp]
		,[WebGrants_Account]
		,_Temp_Converted
		from GDS
		where _Temp_Converted = 0
		order by GDS_ID desc
	</cfquery>
	
	<cfloop query="GetGDSTemps">
		<cfquery name="UpdateGDSTemp" datasource="WMS">
			update GDS
			set Date_Updated = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Now()#" />,
			
			<cfif Len(_HS_Grad_Date_Temp) gt 0>
				HS_Grad_Date = #CreateDate(Left(_HS_Grad_Date_Temp, 4), Mid(_HS_Grad_Date_Temp, 5, 2), Right(_HS_Grad_Date_Temp, 2))#,
			</cfif>
			<cfif Len(_Pell_Grant_Elig_Flag_Temp) gt 0>
				<cfif _Pell_Grant_Elig_Flag_Temp eq "y">
					Pell_Grant_Elig_Flag = 1,
				<cfelseif _Pell_Grant_Elig_Flag_Temp eq "n">
					Pell_Grant_Elig_Flag = 0,
				</cfif>
			</cfif>
			<cfif Len(_Rcv_Public_Bnft_Temp) gt 0>
				<cfif _Rcv_Public_Bnft_Temp eq "y">
					Rcv_Public_Bnft = 1,
				<cfelseif _Rcv_Public_Bnft_Temp eq "n">
					Rcv_Public_Bnft = 0,
				</cfif>
			</cfif>
			<cfif Len(_On_Time_ISIR_Temp) gt 0>
				<cfif _On_Time_ISIR_Temp eq "y">
					On_Time_ISIR = 1,
				<cfelseif _On_Time_ISIR_Temp eq "n">
					On_Time_ISIR = 0,
				</cfif>
			</cfif>
			<cfif Len(_ISIR_Date_Temp) gt 0>
				ISIR_Date = #CreateDate(Left(_ISIR_Date_Temp, 4), Mid(_ISIR_Date_Temp, 5, 2), Right(_ISIR_Date_Temp, 2))#,
			</cfif>
			<cfif Len(_On_Time_GPA_Temp) gt 0>
				<cfif _On_Time_GPA_Temp eq "y">
					On_Time_GPA = 1,
				<cfelseif _On_Time_GPA_Temp eq "n">
					On_Time_GPA = 0,
				</cfif>
			</cfif>
			<cfif Len(_GPA_Date_Temp) gt 0>
				GPA_Date = #CreateDate(Left(_GPA_Date_Temp, 4), Mid(_GPA_Date_Temp, 5, 2), Right(_GPA_Date_Temp, 2))#,
			</cfif>
			<cfif Len(_Non_SSN_GPA_Temp) gt 0>
				<cfif _Non_SSN_GPA_Temp eq "y">
					Non_SSN_GPA = 1,
				<cfelseif _Non_SSN_GPA_Temp eq "n">
					Non_SSN_GPA = 0,
				</cfif>
			</cfif>
			<cfif Len(_On_Time_Dream_Act_Cal_ISIR_Temp) gt 0>
				<cfif _On_Time_Dream_Act_Cal_ISIR_Temp eq "y">
					On_Time_Dream_Act_Cal_ISIR = 1,
				<cfelseif _On_Time_Dream_Act_Cal_ISIR_Temp eq "n">
					On_Time_Dream_Act_Cal_ISIR = 0,
				</cfif>
			</cfif>
			<cfif Len(_WebGrants_Account_Temp) gt 0>
				<cfif _WebGrants_Account_Temp eq "y">
					WebGrants_Account = 1,
				<cfelseif _WebGrants_Account_Temp eq "n">
					WebGrants_Account = 0,
				</cfif>
			</cfif>
			_Temp_Converted = 1,
			Updated_By = 'Ken/Temps'
			where GDS_ID = #GDS_ID#
		</cfquery>
		<cfset lastGDSID = GDS_ID />
	</cfloop>
	<cfoutput>
		<p>Processed #GetGDSTemps.RecordCount# records ending in GDS_ID #lastGDSID#</p>
		<p>
			<a href="#script_name#?updateGDSTemps=true&processQty=#processQty#">#processQty# again</a> | 
			<a href="#script_name#?updateGDSTemps=true&processQty=1000">1000</a> | 
			<a href="#script_name#?updateGDSTemps=true&processQty=2000">2000</a> | 
			<a href="#script_name#?updateGDSTemps=true&processQty=5000">5000</a> | 
			<a href="#script_name#?updateGDSTemps=true&processQty=10000">10000</a> | 
			<a href="#script_name#?updateGDSTemps=true&processQty=20000">20000</a>
		</p>
	</cfoutput>
	<cfif GetGDSTemps.RecordCount eq processQty>
		<!--- <p>Process will automatically run in 5000ms.</p>
		<cfthread action="sleep" duration="5000" />
		<cflocation url="#script_name#?updateGDSTemps=true&processQty=#processQty#" addtoken="no" /> --->
	</cfif>
</cfif>

<!--- score GDS matches --->
<cfparam name="scoreGDSMatches" default="false">
<cfif scoreGDSMatches>
	<!--- <cfquery name="gdsData" datasource="WMS">
		 select top 20 *
		 from GDS_2014_Match2_20140804
	</cfquery> --->

	<!--- <cfdump var="#gdsData#"> --->
		
	<cfscript>
		gdsGateway = new assets.calgrants.com.gateway.GDSGateway();
		gdsData = gdsGateway.queryGDSMatch(programYears = '2013-2014', tableName = 'GDS_Match_20141013');
	</cfscript>
	
	<!--- <cfdump var="#gdsData#"> --->

		<cfset recScore = gdsGateway.scoreGDSMatch(gdsData, "nm") />
		
	<!--- <cfoutput>
		<h4>Match Table: #matchTable#</h4>
		<h4>Match Table: #matchTable#</h4>
	</cfoutput> --->
	
	Done!
	
</cfif>

<!---*******************************************
	Workshop Combinations
	
	Consolidated Despina's workshop combinations into a single worksheet and imported them via DTS into Workshop_Combination_2014.
	
	Create a draw group in Draw_Group_2014 for each combination set in Workshop_Combination_2014. Also, drop the combination list into the Workshop_IDs field in Draw_Group_2014.
	
	Break each combination in Draw_Group_2014 into separate records in Draw_Group_Workshop_2014.
	
	Find workshops that are missing from the combinations but also qualify for a scholarship and create a draw group in Draw_Group_2014 for each.  Also create a record in Draw_Group_Workshop_2014 for each eligible workshop. 
******************************************--->

<!--- workshop combinations are already in Workshop_Combination_2014 from DTS --->
<cfparam name="distributeWorkshopCombos" default="false" />
<cfif distributeWorkshopCombos>
	<cfquery name="GetWorkshopCombinations" datasource="WMS">
		select distinct Workshop_IDs
		from Workshop_Combination_2014
		order by Workshop_IDs
	</cfquery>
	<cfloop query="GetWorkshopCombinations">
		<cfquery name="InsertDrawGroup" datasource="WMS">
			insert into Draw_Group_2014
			(Deleted, Award, Award_Count, Workshop_IDs)
			values
			(0, 'CCFC', 1, '#Replace(Workshop_IDs, "_", ",", "all")#')
			select @@identity as drawGroupID
		</cfquery>
		<cfloop index="wID" list='#Replace(Workshop_IDs, "_", ",", "all")#'>
			<cfquery name="InsertDrawGroupWorkshop" datasource="WMS">
				insert into Draw_Group_Workshop_2014
				(Deleted, Draw_Group_ID, Workshop_ID)
				values
				(0, #InsertDrawGroup.drawGroupID#, #wID#)
			</cfquery>	
		</cfloop>
	</cfloop>
</cfif>
<!--- mark records in Workshop_Student_Count_2014 as Combined if they show up in Draw_Group_Workshop_2014 --->
<!--- did this via query in SQL Server
update Workshop_Student_Count_2014
set Combined = 1
where Workshop_ID in (select Workshop_ID from Draw_Group_Workshop_2014) --->
<!--- create draw groups for the workshops that aren't combined but are eligible (Workshop_Student_Count_2014.Combined = 0 and Student_Count gte 25) --->
<cfparam name="createUncombinedWorkshopDrawGroups" default="false" />
<cfif createUncombinedWorkshopDrawGroups>
	<cfquery name="GetWorkshopsForDrawGroups" datasource="WMS">
		select *
		from Workshop_Student_Count_2014
		where Combined = 0
		and Student_Count >= 25
	</cfquery>
	<cfloop query="GetWorkshopsForDrawGroups">
		<cfquery name="InsertDrawGroup" datasource="WMS">
			insert into Draw_Group_2014
			(Deleted, Award, Award_Count, Workshop_IDs)
			values
			(0, 'CCFC', 1, '#Workshop_ID#')
			select @@identity as drawGroupID
		</cfquery>
		<cfquery name="InsertDrawGroupWorkshop" datasource="WMS">
			insert into Draw_Group_Workshop_2014
			(Deleted, Draw_Group_ID, Workshop_ID)
			values
			(0, #InsertDrawGroup.drawGroupID#, #Workshop_ID#)
		</cfquery>	
	</cfloop>
</cfif>

<!--- randomly rank all Students for Program_Year --->
<!--- in order to seed ranking, created a UUID field in Student and set it to newid().  The creation of the UUID is random and quick.  After that, use the script below to apply
integer ranking (Student._Draw_Group_Rank_2014) for each Draw Group.  If the combinations change, this script can be rerun to re-randomize one or all Draw Groups. --->
<cfparam name="rankDrawGroupsFromUUID" default="false" />
<cfif rankDrawGroupsFromUUID>
	<cfquery name="GetStudents" datasource="WMS">
		SELECT 
		dbo.Draw_Group_2014.Draw_Group_ID, 
		dbo.Student._Randomizer,
		dbo.Student.Student_ID
		FROM dbo.Student LEFT OUTER JOIN
		dbo.GDS ON dbo.Student.Student_ID = dbo.GDS.Student_ID RIGHT OUTER JOIN
		dbo.Draw_Group_Workshop_2014 ON dbo.Student.Workshop_ID = dbo.Draw_Group_Workshop_2014.Workshop_ID RIGHT OUTER JOIN
		dbo.Draw_Group_2014 ON dbo.Draw_Group_Workshop_2014.Draw_Group_ID = dbo.Draw_Group_2014.Draw_Group_ID
		WHERE     (dbo.Student.Deleted = 0)
		ORDER BY dbo.Draw_Group_2014.Draw_Group_ID, dbo.Student._Randomizer
	</cfquery>
	<cfoutput query="GetStudents" group="Draw_Group_ID">
		<cfset rank = 1 />
		<cfoutput>
			<cfquery name="UpdateStudentRank" datasource="WMS">
				update Student
				set _Draw_Group_Rank_2014 = #rank#
				where Student_ID = #Student_ID#
			</cfquery>
			<cfset rank = rank + 1 />
		</cfoutput>	
	</cfoutput>
	<cfoutput>
		Done! Processed #GetStudents.RecordCount# records.
	</cfoutput>
</cfif>

<!--- migrate from update table to master GDS table

	Used DTS.  Moved bit and date fields into temp varchar fields.

 --->
<!--- <cfparam name="migrateGDSMatchData" default="false" />

<cfif migrateGDSMatchData>
	<cfquery name="GetUpdateData" datasource="WMS">
		select *
		from GDS_2014_Match1_20140417
	</cfquery>
	
	<cfloop query="GetUpdateData">
		<cfquery name="InsertGDS" datasource="WMS">
			INSERT INTO [WMS].[dbo].[GDS]
					   (
			[Student_ID],
			[Deleted],
			[Match_Criteria_Nbr],
			[Match_Record_Nbr],
			[SSN Nbr],
			[GDS_Last_Name],
			[GDS_First_Name],
			[GDS_DOB],
			[GDS_Phone],
			[GDS_Str_Address],
			[GDS_City],
			[GDS_State],
			[GDS_Zip],
			[Dep_Stat],
			[App_Type],
			[App_Stat],
			[GRAW],
			[SMLTA],
			[Income],
			[Grant_Prog_Code_1],
			[Grant_Prog_Code_2],
			[Grant_Prog_Code_3],
			[Awd_Stat_Code_1],
			[Awd_Stat_Code_2],
			[Awd_Stat_Code_3],
			[Graw_Amt_1],
			[Graw_Amt_2],
			[Graw_Amt_3],
			[GRAW_Rsn_Code_1],
			[GRAW_Rsn_Code_2],
			[GRAW_Rsn_Code_3],
			[Stdt_St_Res_Code],
			[Stdt_St_Res_Date],
			[Par_St_Res_Code],
			[GPA],
			[Sch_Submitted_GPA],
			[Test_Type],
			[Test_Score],
			[HS_Grad_Date],
			[Grant_Rej_Rsn_Code],
			[Gender],
			[Stdt_Email_Addr],
			[Stdt_Ctzn_Status],
			[Stdt_Residence_State],
			[Deg_Cert],
			[Father_El],
			[Mother_El],
			[Parent_Gross_Income],
			[Sch_Code_1],
			[Sch_Code_2],
			[Sch_Code_3],
			[Dependency_Status],
			[Pell_Grant_Elig_Flag],
			[Reject_Reason_Codes],
			[Student_AGI],
			[Primary_EFC],
			[Secondary_EFC],
			[Parent_Asset_Contrib],
			[Student_Asset_Contrib],
			[Selective_Service_Reg_Flag],
			[SSA_Ctzn_Flag],
			[Nbr_Of_Fam_Mbrs],
			[Nbr_College],
			[Rcv_Public_Bnft],
			[Cycle_ID],
			[On_Time_ISIR],
			[ISIR_Date],
			[ISIR_Source],
			[On_Time_GPA],
			[GPA_Date],
			[Non_SSN_GPA],
			[On_Time_Dream_Act_Cal_ISIR],
			[WebGrants_Account]
			)
			VALUES
			(
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[Student_ID]#" null="false" />,
			<cfqueryparam cfsqltype="cf_sql_bit" value="#[Deleted]#" null="false" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[MATCH CRITERIA NBR]#" null="#IIf(Len(#[MATCH CRITERIA NBR]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[MATCH RECORD NBR]#" null="#IIf(Len(#[MATCH RECORD NBR]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[SSN NBR]#" null="#IIf(Len(#[SSN NBR]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GDS LAST NAME]#" null="#IIf(Len(#[GDS LAST NAME]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GDS FIRST NAME]#" null="#IIf(Len(#[GDS FIRST NAME]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_date" value="#[GDS DOB]#" null="#IIf(Len(#[GDS DOB]#) eq 0, true, false)#" />,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GDS PHONE]#" null="#IIf(Len(#[GDS PHONE]#) eq 0, true, false)#" />,xxxxxxxxxxxxxxxxxxxxxxxxx
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GDS STR ADDR]#" null="#IIf(Len(#[GDS STR ADDR]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GDS CITY]#" null="#IIf(Len(#[GDS CITY]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GDS STATE]#" null="#IIf(Len(#[GDS STATE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GDS ZIP]#" null="#IIf(Len(#[GDS ZIP]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[DEP STAT]#" null="#IIf(Len(#[DEP STAT]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[APP TYPE]#" null="#IIf(Len(#[APP TYPE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[APP STAT]#" null="#IIf(Len(#[APP STAT]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[GRAW]#" null="#IIf(Len(#[GRAW]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[SMLTA]#" null="#IIf(Len(#[SMLTA]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[INCOME]#" null="#IIf(Len(#[INCOME]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GRANT PROG CODE 1]#" null="#IIf(Len(#[GRANT PROG CODE 1]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GRANT PROG CODE 2]#" null="#IIf(Len(#[GRANT PROG CODE 2]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GRANT PROG CODE 3]#" null="#IIf(Len(#[GRANT PROG CODE 3]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[AWD STAT CODE 1]#" null="#IIf(Len(#[AWD STAT CODE 1]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[AWD STAT CODE 2]#" null="#IIf(Len(#[AWD STAT CODE 2]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[AWD STAT CODE 3]#" null="#IIf(Len(#[AWD STAT CODE 3]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[GRAW AMT 1]#" null="#IIf(Len(#[GRAW AMT 1]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[GRAW AMT 2]#" null="#IIf(Len(#[GRAW AMT 2]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[GRAW AMT 3]#" null="#IIf(Len(#[GRAW AMT 3]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[GRAW RSN CODE 1]#" null="#IIf(Len(#[GRAW RSN CODE 1]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[GRAW RSN CODE 2]#" null="#IIf(Len(#[GRAW RSN CODE 2]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[GRAW RSN CODE 3]#" null="#IIf(Len(#[GRAW RSN CODE 3]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[STDT ST RES CODE]#" null="#IIf(Len(#[STDT ST RES CODE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[STDT ST RES DATE]#" null="#IIf(Len(#[STDT ST RES DATE]]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[PAR ST RES CODE]#" null="#IIf(Len(#[PAR ST RES CODE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[GPA]#" null="#IIf(Len(#[GPA]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[SCH SUBMITTED GPA]#" null="#IIf(Len(#[SCH SUBMITTED GPA]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[TEST TYPE]#" null="#IIf(Len(#[TEST TYPE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[TEST SCORE]#" null="#IIf(Len(#[TEST SCORE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_date" value="#[HS GRAD DATE]#" null="#IIf(Len(#[HS GRAD DATE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[GRANT REJ RSN CODE]#" null="#IIf(Len(#[GRANT REJ RSN CODE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[GENDER]#" null="#IIf(Len(#[GENDER]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[STDT EMAIL ADDR]#" null="#IIf(Len(#[STDT EMAIL ADDR]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[STDT CTZN STATUS]#" null="#IIf(Len(#[STDT CTZN STATUS]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[STDT RESIDENCE STATE]#" null="#IIf(Len(#[STDT RESIDENCE STATE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[DEG/CERT]#" null="#IIf(Len(#[DEG/CERT]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[FATHER EL]#" null="#IIf(Len(#[FATHER EL]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[MOTHER EL]#" null="#IIf(Len(#[MOTHER EL]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[PARENT GROSS INCOME]#" null="#IIf(Len(#[PARENT GROSS INCOME]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[SCH CODE 1]#" null="#IIf(Len(#[SCH CODE 1]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[SCH CODE 2]#" null="#IIf(Len(#[SCH CODE 2]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[SCH CODE 3]#" null="#IIf(Len(#[SCH CODE 3]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[DEPENDENCY STATUS]#" null="#IIf(Len(#[DEPENDENCY STATUS]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_bit" value="#[PELL GRANT ELIG FLAG]#" null="#IIf(Len(#[PELL GRANT ELIG FLAG]#) eq 0, true, false)#" />,xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[REJECT REASON CODES]#" null="#IIf(Len(#[REJECT REASON CODES]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[STUDENT AGI]#" null="#IIf(Len(#[STUDENT AGI]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[PRIMARY EFC]#" null="#IIf(Len(#[PRIMARY EFC]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[SECONDARY EFC]#" null="#IIf(Len(#[SECONDARY EFC]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[PARENT ASSET CONTRIB]#" null="#IIf(Len(#[PARENT ASSET CONTRIB]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_float" value="#[STUDENT ASSET CONTRIB]#" null="#IIf(Len(#[STUDENT ASSET CONTRIB]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[SELECTIVE SERVICE REG FLAG]#" null="#IIf(Len(#[SELECTIVE SERVICE REG FLAG]#) eq 0, true, false)#" />,xxxxxxxxxxxxxxx
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[SSA CTZN FLAG]#" null="#IIf(Len(#[SSA CTZN FLAG]#) eq 0, true, false)#" />,xxxxxxxxxxxxxxxxxxxx
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[NBR OF FAM MBRS]#" null="#IIf(Len(#[NBR OF FAM MBRS]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_integer" value="#[NBR COLLEGE]#" null="#IIf(Len(#[NBR COLLEGE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_bit" value="#[RCV PUBLIC BNFT]#" null="#IIf(Len(#[RCV PUBLIC BNFT]#) eq 0, true, false)#" />,xxxxxxxxxxxxxxxx
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[CYCLE ID]#" null="#IIf(Len(#[CYCLE ID]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_bit" value="#[ON TIME ISIR]#" null="#IIf(Len(#[ON TIME ISIR]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_date" value="#[ISIR DATE]#" null="#IIf(Len(#[ISIR DATE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_varchar" value="#[ISIR SOURCE]#" null="#IIf(Len(#[ISIR SOURCE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_bit" value="#[ON TIME GPA]#" null="#IIf(Len(#[ON TIME GPA]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_date" value="#[GPA DATE]#" null="#IIf(Len(#[GPA DATE]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_bit" value="#[NON-SSN GPA]#" null="#IIf(Len(#[NON-SSN GPA]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_bit" value="#[ON TIME DREAM ACT CAL ISIR]#" null="#IIf(Len(#[ON TIME DREAM ACT CAL ISIR]#) eq 0, true, false)#" />,
			<cfqueryparam cfsqltype="cf_sql_bit" value="#[WEBGRANTS4STUDENTS ACCOUNT]#" null="#IIf(Len(#[WEBGRANTS4STUDENTS ACCOUNT]#) eq 0, true, false)#" />
			)
		</cfquery>

	</cfloop>
	
	migrateGDSMatchData done.
</cfif> --->

<!--- Update GDS records that were "Unmatched" through the Super User Admin GDS interface but before the then current stored proc for destroyGDS had been launched.
Difference between version was that old version set GDS.Deleted = 1.  Current version sets all GDS related fields, including and especially Match_Criteria_Nbr and any fields related to scholarship
qualification, to Null while leaving GDS.Deleted = 0 --->
<cfparam name="updateDeletedGDS" default="false" />
<cfif updateDeletedGDS>
	<cfquery name="GetDeletedGDS" datasource="WMS">
		select *
		from GDS
		where Deleted = 1
	</cfquery>
	
	<cfloop query="GetDeletedGDS">
		<cfquery name="UpdateGDS" datasource="WMS">
			update GDS set
			Deleted = 0
			,Match_Criteria_Nbr = NULL
			,Match_Record_Nbr = NULL
			,SSN_Nbr = NULL
			,GDS_Last_Name = NULL
			,GDS_First_Name = NULL
			,GDS_DOB = NULL
			,GDS_Phone = NULL
			,GDS_Str_Address = NULL
			,GDS_City = NULL
			,GDS_State = NULL
			,GDS_Zip = NULL
			,Dep_Stat = NULL
			,App_Type = NULL
			,App_Stat = NULL
			,GRAW = NULL
			,SMLTA = NULL
			,Income = NULL
			,Grant_Prog_Code_1 = NULL
			,Grant_Prog_Code_2 = NULL
			,Grant_Prog_Code_3 = NULL
			,Awd_Stat_Code_1 = NULL
			,Awd_Stat_Code_2 = NULL
			,Awd_Stat_Code_3 = NULL
			,Graw_Amt_1 = NULL
			,Graw_Amt_2 = NULL
			,Graw_Amt_3 = NULL
			,GRAW_Rsn_Code_1 = NULL
			,GRAW_Rsn_Code_2 = NULL
			,GRAW_Rsn_Code_3 = NULL
			,Stdt_St_Res_Code = NULL
			,Stdt_St_Res_Date = NULL
			,Par_St_Res_Code = NULL
			,GPA = NULL
			,Sch_Submitted_GPA = NULL
			,Test_Type = NULL
			,Test_Score = NULL
			,HS_Grad_Date = NULL
			,Grant_Rej_Rsn_Code = NULL
			,Gender = NULL
			,Stdt_Email_Addr = NULL
			,Stdt_Ctzn_Status = NULL
			,Stdt_Residence_State = NULL
			,Deg_Cert = NULL
			,Father_El = NULL
			,Mother_El = NULL
			,Parent_Gross_Income = NULL
			,Sch_Code_1 = NULL
			,Sch_Code_2 = NULL
			,Sch_Code_3 = NULL
			,Dependency_Status = NULL
			,Pell_Grant_Elig_Flag = NULL
			,Reject_Reason_Codes = NULL
			,Student_AGI = NULL
			,Primary_EFC = NULL
			,Secondary_EFC = NULL
			,Parent_Asset_Contrib = NULL
			,Student_Asset_Contrib = NULL
			,Selective_Service_Reg_Flag = NULL
			,SSA_Ctzn_Flag = NULL
			,Nbr_Of_Fam_Mbrs = NULL
			,Nbr_College = NULL
			,Rcv_Public_Bnft = NULL
			,Cycle_ID = NULL
			,On_Time_ISIR = NULL
			,ISIR_Date = NULL
			,ISIR_Source = NULL
			,On_Time_GPA = NULL
			,GPA_Date = NULL
			,Non_SSN_GPA = NULL
			,On_Time_Dream_Act_Cal_ISIR = NULL
			,WebGrants_Account = NULL
			,Stdt_St_Res_Date_Temp = NULL
			,HS_Grad_Date_Temp = NULL
			,Pell_Grant_Elig_Flag_Temp = NULL
			,Rcv_Public_Bnft_Temp = NULL
			,On_Time_ISIR_Temp = NULL
			,ISIR_Date_Temp = NULL
			,On_Time_GPA_Temp = NULL
			,GPA_Date_Temp = NULL
			,Non_SSN_GPA_Temp = NULL
			,On_Time_Dream_Act_Cal_ISIR_Temp = NULL
			,WebGrants_Account_Temp = NULL
		WHERE GDS_ID = #GDS_ID#
		</cfquery>
	</cfloop>
	Done with update deleted GDS
</cfif>

<!--- Add verification scores (Similarity, Commonality, Continuity, Score, Bonus) and auto update status to GDS records leading to auto-unmatching --->
<cfparam name="addScoresToGDS" default="false">
<cfif addScoresToGDS>
	<cfscript>
		base = new assets.calgrants.com.Base();
		gdsGateway = new assets.calgrants.com.gateway.GDSGateway();
		gds1 = gdsGateway.query(programYears = '2013-2014');
	</cfscript>
	<cfquery name="gds" dbtype="query" maxrows="5000">
		select *
		from gds1
		where Match_Criteria is not null
		and Auto_Status is Null
	</cfquery>
	<cfloop query="gds">
		<cfscript>
			gdsStruct = structNew();
			//gdsStruct.Student_ID = Student_ID;
			gdsStruct.First_Name = Replace(REReplace(UCase(Trim(First_Name)), "[^A-Z\ \-\']", "", "all"), "  ", " ", "all");
			gdsStruct.Last_Name = Replace(REReplace(UCase(Trim(Last_Name)), "[^A-Z\ \-\']", "", "all"), "  ", " ", "all");
			//gdsStruct.Full_Name = gdsStruct.First_Name & " " & gdsStruct.Last_Name;
			gdsStruct.Address = Replace(REReplace(UCase(Trim(Address)), "[^A-Z0-9\ ]", "", "all"), "  ", " ", "all");
			gdsStruct.City = Replace(REReplace(UCase(Trim(City)), "[^A-Z\ ]", "", "all"), "  ", " ", "all");
			gdsStruct.Zip_Code = Left(REReplace(Trim(Zip_Code), "[^0-9]", "", "all"), 5);
			//gdsStruct.Full_Address = gdsStruct.Address & " " & gdsStruct.City & " " & gdsStruct.Zip_Code;
			gdsStruct.Email = REReplace(UCase(Trim(Email)), "[^A-Z0-9\@\.\_\-]", "", "all");
			gdsStruct.SSN = Right(REReplace(Trim(SSN), "[^0-9]", "", "all"), 4);
			// Student
			studentStruct = structNew();
			//studentStruct.Student_ID = Student_ID;
			studentStruct.First_Name = Replace(REReplace(UCase(Trim(Student_First_Name)), "[^A-Z\ \-\']", "", "all"), "  ", " ", "all");
			studentStruct.Last_Name = Replace(REReplace(UCase(Trim(Student_Last_Name)), "[^A-Z\ \-\']", "", "all"), "  ", " ", "all");
			//studentStruct.Full_Name = studentStruct.First_Name & " " & studentStruct.Last_Name;
			studentStruct.Address = Replace(REReplace(UCase(Trim(Student_Address)), "[^A-Z0-9\ ]", "", "all"), "  ", " ", "all");
			studentStruct.City = Replace(REReplace(UCase(Trim(Student_City)), "[^A-Z\ ]", "", "all"), "  ", " ", "all");
			studentStruct.Zip_Code = Left(REReplace(Trim(Student_Zip_Code), "[^0-9]", "", "all"), 5);
			//studentStruct.Full_Address = studentStruct.Address & " " & studentStruct.City & " " & studentStruct.Zip_Code;
			studentStruct.Email = REReplace(UCase(Trim(Student_Email)), "[^A-Z0-9\@\.\_\-]", "", "all");
			studentStruct.SSN = Right(REReplace(Trim(Student_SSN), "[^0-9]", "", "all"), 4);
			if (isDate(DOB)) {
				gdsStruct.DOB = DateFormat(DOB, "mm/dd/yyyy");
			} else {
				gdsStruct.DOB = "";
			}
			if (isDate(Student_DOB)) {
				studentStruct.DOB = DateFormat(Student_DOB, "mm/dd/yyyy");
			} else {
				studentStruct.DOB = "";
			}
			gdsStruct.Phone = REReplace(Trim(Phone), "[^0-9]", "", "all");
			usePhone = base.pickPhone(Phone, Student_Phone1, Student_Phone2);
			if (usePhone eq "phone1") {
				studentStruct.Phone = REReplace(Trim(Student_Phone1), "[^0-9]", "", "all");
			} else {
				studentStruct.Phone = REReplace(Trim(Student_Phone2), "[^0-9]", "", "all");
			}
			// Do the comparison! struct1 is GDS, struct2 is Student
			compareResult = base.scoreStructs(gdsStruct, studentStruct);
			
			perfectDiscount = compareResult.perfect * .025;
			bonusDiscount = compareResult.bonus * .025;
			totalBonus = perfectDiscount + bonusDiscount;
			matchThreshold = .7 - totalBonus;
			unmatchThreshold = .5;
			if (compareResult.score gte matchThreshold) {
				autoStatus = "Match";
			} else if (compareResult.score lte unmatchThreshold) {
				autoStatus = "Unmatch";
			} else {
				autoStatus = "Review";
			}
		</cfscript>

		<cfquery name="UpdateGDS" datasource="WMS">
			update GDS
			set Similarity = #compareResult.similarity#,
			Commonality = #compareResult.commonality#,
			Continuity = #compareResult.continuity#,
			Score = #compareResult.score#,
			Perfect = #compareResult.perfect#,
			Bonus = #compareResult.bonus#,
			Auto_Status = '#autoStatus#'
			where GDS_ID = #GDS_ID#			
		</cfquery>
	</cfloop>
	<cfoutput>
		Added scoring for #gds.RecordCount# GDS records
	</cfoutput>
</cfif>

<!--- The moment we have all been waiting for...auto-update unmatches --->
<cfscript>
	gdsGateway = new assets.calgrants.com.gateway.GDSGateway();
</cfscript>
<cfparam name="autoUpdateUnmatch" default="false" />
<cfif autoUpdateUnmatch>
	<cfquery name="GetUnmatch" datasource="WMS">
		select GDS_ID, Auto_Status
		from GDS
		where Program_Year = '2013-2014'
		and Auto_Status = 'Unmatch'
		and Match_Criteria_Nbr is not null
	</cfquery>
	<cfloop query="GetUnmatch">
		<cfset destroyResult = gdsGateway.unmatch(GDS_ID = GDS_ID) />
	</cfloop>
	<cfoutput>
	Done unmatching #GetUnmatch.RecordCount# records
	</cfoutput>
</cfif>

<!--- generate scholarship Recipipient list which lists top 10 ranked students from each draw group --->
<cfparam name="generateScholarshipRecipientList" default="false">
<cfif generateScholarshipRecipientList>
	<cfquery name="GetScholarshipCandidates" datasource="WMS">
		select *
		from base_selectScholarshipRecipients
		order by draw_group_id, rank
	</cfquery>
Status,Draw_Group_ID,Workshop_ID,Student_ID,GDS_ID,Rank,Match_Criteria_Nbr,Auto_Status,Award,First_Name,Last_Name,Address,City,Zip_Code,Phone1,Phone2,Email,DOB,SSN,Award_Count,Site,Site_Type,HS_Grad_Date,On_Time_ISIR,On_Time_Dream_Act_Cal_ISIR,GPA,Region_ID,SSN_Nbr,GDS_First_Name,GDS_Last_Name,GDS_Address,GDS_City,GDS_State,GDS_Zip,GDS_Phone,GDS_Email,GDS_DOB,Pell_Grant_Elig_Flag,Primary_EFC,Gender,Sch_Submitted_GPA,CEEB_ID,High_School<br />
<cfoutput query="GetScholarshipCandidates" group="Draw_Group_ID">
<cfset sel = false />
<cfset selStudentID = 0 />
<cfoutput>
<cfif Len(Match_Criteria_Nbr) gt 0 and Len(GPA) gt 0 and (On_Time_ISIR eq 1 or On_Time_Dream_Act_Cal_ISIR eq 1) and IsDate(HS_Grad_Date) and Year(HS_Grad_Date) eq 2014>
<cfif not sel><cfset selStudent_ID = Student_ID /><cfset sel = true>W,<cfelse>Q,</cfif><cfelse>D,</cfif>#Draw_Group_ID#,#Workshop_ID#,#Student_ID#,#GDS_ID#,#Rank#,#Match_Criteria_Nbr#,#Auto_Status#,#Award#,#First_Name#,#Last_Name#,#Address#,#City#,#Zip_Code#,#Phone1#,#Phone2#,#Email#,#DateFormat(DOB)#,#SSN#,#Award_Count#,#Site#,#Site_Type#,#DateFormat(HS_Grad_Date)#,#On_Time_ISIR#,#On_Time_Dream_Act_Cal_ISIR#,#GPA#,#Region_ID#,#SSN_Nbr#,#GDS_First_Name#,#GDS_Last_Name#,#GDS_Address#,#GDS_City#,#GDS_State#,#GDS_Zip#,#GDS_Phone#,#GDS_Email#,#DateFormat(GDS_DOB)#,#Pell_Grant_Elig_Flag#,#Primary_EFC#,#Gender#,#Sch_Submitted_GPA#,#CEEB_ID#,#High_School#<br />
</cfoutput>
</cfoutput>
</cfif>

<!--- Recipient list was reviewed and issues resolved.  Yreka (Workshop_ID 4455, Draw_Group_ID 444) was selected manually.  8 workshops didn't have recipients in the top 10 and were reviewed until a recipient was found, if any.  4 Draw Groups didn't have a recipient in the end --->

<!--- after recipients were verified, a list of Student_ID were imported from Excel to be used for the migration to Scholarships.  Table name Scholarship_Recipients_2014 --->

<!--- migrate 2014 scholarship recipients --->
<cfparam name="migrateScholarships" default="false">
<cfif migrateScholarships>
	<cfquery name="GetScholarshipRecipients2014" datasource="WMS">
SELECT     dbo.Scholarship_Recipients_2014.Draw_Group_ID, dbo.Scholarship_Recipients_2014.Workshop_ID, dbo.Scholarship_Recipients_2014.Student_ID, 
dbo.Scholarship_Recipients_2014.GDS_ID, dbo.Student.Origin, dbo.Scholarship_Recipients_2014.Status, dbo.Scholarship_Recipients_2014.Rank, 
dbo.Scholarship_Recipients_2014.Match_Criteria_Nbr, dbo.Student.First_Name, dbo.Student.Last_Name, dbo.Student.Deleted
FROM         dbo.Scholarship_Recipients_2014 LEFT OUTER JOIN
dbo.Student ON dbo.Scholarship_Recipients_2014.Student_ID = dbo.Student.Student_ID
	</cfquery>
	<cfoutput>
		<cfloop query="GetScholarshipRecipients2014">
			<cfquery name="GetScholarship" datasource="WMS">
				select *
				from Scholarship
				where Student_ID = #Student_ID#
			</cfquery>
			<cfif GetScholarship.RecordCount eq 0>
				<cfquery name="GetGDS" datasource="WMS">
					select *
					from GDS
					where Student_ID = #Student_ID#
				</cfquery>
				<cfif GetGDS.RecordCount gt 0>
					<cfset firstName = titleCase(GetGDS.GDS_First_Name) />
					<cfset lastName = titleCase(GetGDS.GDS_Last_Name) />
				<cfelse>
					<cfset firstName = titleCase(First_Name) />
					<cfset lastName = titleCase(Last_Name) />
				</cfif>
				<cfset claimID = IIf(LCase(Origin) eq "web", DE("W"), DE("P")) & Workshop_ID & Student_ID>
				<cfquery name="InsertScholarship" datasource="WMS">
					insert Scholarship
					(Student_ID, Claim_ID, Cohort, Scholarship_Type, PBS, First_Name, Last_Name, Institution1_ID, Institution2_ID, Institution3_ID)
					values
					(#Student_ID#, '#claimID#', 6, 'CCFC', 0, '#firstName#', '#lastName#', 0, 0, 0)
				</cfquery>
			<cfelse>
				<p>Student_ID #Student_ID# already in Scholarship as #GetScholarship.Scholarship_ID#</p>
			</cfif>
		</cfloop>
	</cfoutput>
	<h1>Scholarships migrated</h1>
</cfif>

<!--- populate unpopulated Fund Payee data --->

<cfparam name="updateFundPayees" default="false">
<cfif updateFundPayees>
	<cfquery name="GetFunds" datasource="WMS">
		select *
		from Fund
		where Fund_ID in (785,
		800,
		813,
		825,
		831,
		855,
		864,
		875,
		923,
		936,
		947,
		949,
		962,
		981,
		998,
		999)
	</cfquery>
	
	<cfloop query="GetFunds">
		<cfquery name="GetRequestor" datasource="WMS">
			select [user].first_name, [user].last_name, organization.organization, organization.address, organization.city, organization.zip_code, [user].phone, [user].extension, [user].email
			from [User] LEFT OUTER JOIN [Organization]
			on [User].Organization_ID = Organization.Organization_ID
			where [user].User_ID = #Requested_By_ID#
		</cfquery>
		<cfquery name="UpdateFund" datasource="WMS">
			update Fund
			set Deleted = #Deleted#,
			<cfif Len(Trim(Payee_First_Name)) eq 0>
				Payee_First_Name = '#GetRequestor.First_Name#',
			</cfif>
			<cfif Len(Trim(Payee_Last_Name)) eq 0>
				Payee_Last_Name = '#GetRequestor.Last_Name#',
			</cfif>
			<cfif Len(Trim(Payee_Organization)) eq 0>
				Payee_Organization = '#GetRequestor.Organization#',
			</cfif>
			<cfif Len(Trim(Payee_Department)) eq 0>
				Payee_Department = 'Counseling Office',
			</cfif>
			<cfif Len(Trim(Payee_Address)) eq 0>
				Payee_Address = '#GetRequestor.Address#',
			</cfif>
			<cfif Len(Trim(Payee_City)) eq 0>
				Payee_City = '#GetRequestor.City#',
			</cfif>
			<cfif Len(Trim(Payee_Zip_Code)) eq 0>
				Payee_Zip_Code = '#GetRequestor.Zip_Code#',
			</cfif>
			<cfif Len(Trim(Payee_Phone)) eq 0>
				Payee_Phone = '#GetRequestor.Phone#',
			</cfif>
			<cfif Len(Trim(Payee_Email)) eq 0>
				Payee_Email = '#GetRequestor.Email#',
			</cfif>
			Approved = #Approved#
			where Requested_By_ID = #Requested_By_ID#
		</cfquery>
	</cfloop>
	Done with updateFundPayee
</cfif>

